﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data;
using System.IO;
using Microsoft.Reporting.WinForms;
using System.Runtime.Serialization.Formatters.Binary;

namespace SSISComponents.ReportGenerator
{
    // This attribute tells SSIS about your Task.
    [DtsTask(
        DisplayName = "Task 'ReportGenerator'",
        IconResource = "SSISComponents.ReportGenerator.SSISComponents.ReportGenerator.ico",
        Description = "The task generates reports from a given recordset or generate reports from SSRS 2008.",
        RequiredProductLevel = DTSProductLevel.None,
        UITypeName = "SSISComponents.ReportGenerator.ReportGeneratorUIInterface, SSISComponents.DTS.Tasks.ReportGeneratorTask, Version=1.5.5.0, Culture=neutral, PublicKeyToken=34fdc1d09bed9884")]
    public class ReportGenerator : Task
    {
        #region Private Variables
        
        private string _Reportname;
        private string _Recordset;
        private string _Filename;
        private string _Datasetname;
        private string _ReportType;
        private string _ReportServer;
        private string _Username;
        private string _Password;
        private string _PrefixFilename;
        private string _localname;
        private string _Reportparameter;
        private int _TimeOut = 30000;
        private Boolean _WindowsAuthorization = true;
        private Boolean _Prefix = true;
        private Boolean _Snapshot = false;
        private Boolean _DebugMode = false;
        private IList<Stream> m_streams;
        private bool _refire = false;
        Stream _stream;
                
        #endregion

        #region Constructor
            public ReportGenerator() { }
        #endregion

        #region Public Properties

            /// <summary>
            /// Name and apth to the server report
            /// </summary>
            public int TimeOut
            {
                get { return this._TimeOut; }
                set { this._TimeOut = value; }
            }

        /// <summary>
        /// Name and apth to the server report
        /// </summary>
        public string Reportname
        {
            get { return this._Reportname; }
            set { this._Reportname = value; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string Reportparameter
        {
            get { return this._Reportparameter; }
            set { this._Reportparameter = value; }
        }
        
        /// <summary>
        /// name of dataset defined in the local report
        /// </summary>
        public string Datasetname
        {
            get { return this._Datasetname; }
            set { this._Datasetname = value; }
        }

        /// <summary>
        /// recordset to be displayed on the report
        /// </summary>
        public string Recordset
        {
            get { return this._Recordset; }
            set { this._Recordset = value; }
        }

        /// <summary>
        /// filename
        /// </summary>
        public string Filename
        {
            get { return this._Filename; }
            set { this._Filename = value; }
        }

        /// <summary>
        /// address/url of the report server
        /// </summary>
        public string ReportServer
        {
            get { return this._ReportServer; }
            set { this._ReportServer = value; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string ReportType
        {
            get { return this._ReportType; }
            set { this._ReportType = value; }
        }

        /// <summary>
        /// username
        /// </summary>
        public string Username
        {
            get { return this._Username; }
            set { this._Username = value; }
        }

        /// <summary>
        /// password
        /// </summary>
        public string Password
        {
            get { return this._Password; }
            set { this._Password = value; }
        }

        /// <summary>
        /// filename with datetime as prefix
        /// </summary>
        public string PrefixFilename
        {
            get { return this._PrefixFilename; }
            set { this._PrefixFilename = value; }
        }

        /// <summary>
        /// Get or set value for windows integrated security
        /// </summary>
        public Boolean WindowsAuthorization
        {
            get { return this._WindowsAuthorization; }
            set { this._WindowsAuthorization = value; }
        }

        /// <summary>
        /// get or set the value for the local prefix
        /// </summary>
        public Boolean Prefix
        {
            get { return this._Prefix; }
            set { this._Prefix = value; }
        }

        /// <summary>
        /// show report in debugmode
        /// </summary>
        public Boolean DebugMode
        {
            get { return this._DebugMode; }
            set { this._DebugMode = value; }
        }

        /// <summary>
        /// create snapshot
        /// </summary>
        public Boolean Snapshot
        {
            get { return this._Snapshot; }
            set { this._Snapshot = value; }
        }

        #endregion

        #region Task Implementation
        
        #region InitializeTask
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connections"></param>
        /// <param name="variableDispenser"></param>
        /// <param name="events"></param>
        /// <param name="log"></param>
        /// <param name="eventInfos"></param>
        /// <param name="logEntryInfos"></param>
        /// <param name="refTracker"></param>
        public override void InitializeTask(Connections connections, VariableDispenser variableDispenser, IDTSInfoEvents events, IDTSLogging log, EventInfos eventInfos, LogEntryInfos logEntryInfos, ObjectReferenceTracker refTracker)
        {
            base.InitializeTask(connections, variableDispenser, events, log, eventInfos, logEntryInfos, refTracker);
        }
        
        #endregion

        #region Validate
        
        /// <summary>
        /// Validate local parameter
        /// </summary>
        public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
        {
            Boolean IsBaseValid = true;

            if (!(base.Validate(connections, variableDispenser, componentEvents, log) == DTSExecResult.Success))
            {
                componentEvents.FireError(0, "ReportGeneratorTask", "Base validation failed", "", 0);
                IsBaseValid = false;
            }
            if (string.IsNullOrEmpty(this._Filename))
            {
                componentEvents.FireError(0, "ReportGeneratorTask", "A file name is required.", "", 0);
                IsBaseValid = false;
            }
            if (string.IsNullOrEmpty(this._Recordset) && _ReportType == "Dataset Report")
            {
                componentEvents.FireError(0, "ReportGeneratorTask", "A recordset is required.", "", 0);
                IsBaseValid = false;
            }
            if (string.IsNullOrEmpty(this._Reportname))
            {
                componentEvents.FireError(0, "ReportGeneratorTask", "A Reportname is required.", "", 0);
                IsBaseValid = false;
            }
            if (string.IsNullOrEmpty(this._Datasetname) && _ReportType == "Dataset Report")
            {
                componentEvents.FireError(0, "ReportGeneratorTask", "A Datasetname is required.", "", 0);
                IsBaseValid = false;
            }
            if (_ReportType != "Dataset Report")
            {
                if (!WindowsAuthorization)
                {
                    if (string.IsNullOrEmpty(this._Username))
                    {
                        componentEvents.FireError(0, "ReportGeneratorTask", "A Username is required.", "", 0);
                        IsBaseValid = false;
                    }
                    if (string.IsNullOrEmpty(this._Password))
                    {
                        componentEvents.FireError(0, "ReportGeneratorTask", "A Password is required.", "", 0);
                        IsBaseValid = false;
                    }
                }
            }

            if (IsBaseValid)
            {
                return DTSExecResult.Success;
            }
            else
            {
                return DTSExecResult.Failure;
            }

        }
        
        #endregion

        #region Execute

        /// <summary>
        /// This method is called at runtime, and does all the "work".
        /// </summary>
        public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
        {
            _localname = Localname();

            if (_ReportType == "Dataset Report")
            {
                Variables vars = null;
                System.Data.OleDb.OleDbDataAdapter oleda = new System.Data.OleDb.OleDbDataAdapter();
                System.Data.DataTable dt = new System.Data.DataTable(Datasetname);
                variableDispenser.LockForRead(this.Recordset);
                variableDispenser.GetVariables(ref vars);
                componentEvents.FireInformation(0, "ReportGenerator", "Filename: " + _localname, string.Empty, 0, ref _refire);
                try
                {
                    Microsoft.Reporting.WinForms.ReportViewer rv = new Microsoft.Reporting.WinForms.ReportViewer();
                    rv.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local;
                    Object val = vars[this._Recordset].Value;
                    oleda.Fill(dt, val);
                    componentEvents.FireInformation(0, "ReportGenerator", "Starts report generation with " + dt.Rows.Count.ToString() + " rows", string.Empty, 0, ref _refire);
                    Run(Reportname, Datasetname, dt, _localname);
                    componentEvents.FireInformation(0, "ReportGenerator", "The report was created.", string.Empty, 0, ref _refire);

                    //Warning: debugmode is a user defined option.
                    if (_DebugMode)
                    {
                        componentEvents.FireInformation(0, "ReportGenerator", "Satrts local report generation", string.Empty, 0, ref _refire);
                        SSISComponents.ReportGenerator.ReportViewerUI localReport = new SSISComponents.ReportGenerator.ReportViewerUI();
                        localReport.Datasetname = Datasetname;
                        localReport.Data = dt;
                        localReport.Reportname = Reportname;
                        localReport.ShowDialog();
                    }
                }
                catch (Exception ex)
                {
                    componentEvents.FireError(0, "ReportGeneratorTask", ex.ToString(), "", 0);
                }
                finally
                {
                    if (vars.Locked)
                    {
                        vars.Unlock();
                    }
                }
            }
            else
            {
                GenerateServerReport(variableDispenser, componentEvents);
            }


            // set prefixfilename to variable
            if (_PrefixFilename != null && _PrefixFilename != "" && _PrefixFilename != "Choose a Variable")
            {
                SetPrefixFilename(variableDispenser, componentEvents);
            }

            return base.Execute(connections, variableDispenser, componentEvents, log, transaction);
        }
        
        #endregion
       
        /// <summary>
        /// Routine to provide to the report renderer
        /// </summary>
        /// <param name="name"></param>
        /// <param name="fileNameExtension"></param>
        /// <param name="encoding"></param>
        /// <param name="mimeType"></param>
        /// <param name="willSeek"></param>
        /// <returns></returns>
        private Stream CreateStream(string name, string fileNameExtension, Encoding encoding, string mimeType, bool willSeek)
        {
            _stream = new FileStream(_localname, FileMode.Create);
            m_streams.Add(_stream);
            return _stream;
        }

        /// <summary>
        /// Export the local Report
        /// </summary>
        /// <param name="report"></param>
        private void Export(LocalReport report, string localname)
        {
            FileInfo fi = new FileInfo(localname);
            Warning[] warnings;
            m_streams = new List<Stream>();

            RenderExtensions renderExtensions = new RenderExtensions();
            RenderExtension renderExtension = renderExtensions.Get(fi.Extension);
            if (renderExtension == null) throw new Exception("Not supported render type (Export): " + fi.Extension);
            report.Render(renderExtension.Name, DeviceInfo(fi.Extension), CreateStream, out warnings);

            foreach (Stream stream in m_streams)
            {
                stream.Position = 0;
            }
            _stream.Close();
            _stream.Dispose();
        }

        /// <summary>
        /// Start report generation
        /// </summary>
        /// <param name="reportname">path to the rpeort</param>
        /// <param name="datasetname">datasetname inside the rpeort</param>
        /// <param name="data">data to render</param>
        /// <param name="localname">save the report as...</param>
        private void Run(string reportname, string datasetname, DataTable data, string localname)
        {
            LocalReport report = new LocalReport();
            report.ReportPath = reportname;
            report.DataSources.Add(new ReportDataSource(datasetname, data));
            Export(report, localname);
        }

        /// <summary>
        /// Reporting Services Device Information Settings
        /// http://msdn.microsoft.com/en-us/library/ms155397.aspx
        /// </summary>
        /// <param name="Path"></param>
        /// <returns></returns>
        private string DeviceInfo(string Extension)
        {
            string deviceinfo = null;
            switch (Extension)
            {
                case ".pdf":
                    deviceinfo = "<DeviceInfo>" +
                                "  <OutputFormat>PDF</OutputFormat>" +
                                "  <PageWidth>8.5in</PageWidth>" +
                                "  <PageHeight>11in</PageHeight>" +
                                "  <MarginTop>0.5in</MarginTop>" +
                                "  <MarginLeft>1in</MarginLeft>" +
                                "  <MarginRight>1in</MarginRight>" +
                                "  <MarginBottom>0.5in</MarginBottom>" +
                                "</DeviceInfo>";
                    break;
                case ".xls":
                    deviceinfo = "<DeviceInfo>" +
                                "  <OutputFormat>Excel</OutputFormat>" +
                                "  <PageWidth>8.5in</PageWidth>" +
                                "  <PageHeight>11in</PageHeight>" +
                                "  <MarginTop>0.5in</MarginTop>" +
                                "  <MarginLeft>1in</MarginLeft>" +
                                "  <MarginRight>1in</MarginRight>" +
                                "  <MarginBottom>0.5in</MarginBottom>" +
                                "</DeviceInfo>";
                    break;
            }
            return deviceinfo;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Value"></param>
        /// <returns></returns>
        private string TwoChar(string Value)
        {
            if (Value.Length == 1)
            {
                Value = "0" + Value;
            }
            return Value;
        }

        /// <summary>
        /// Generate SSRS Report
        /// </summary>
        /// <param name="componentEvents"></param>
        private void GenerateServerReport(VariableDispenser variableDispenser, IDTSComponentEvents componentEvents)
        {
            FileInfo fi = new FileInfo(_Filename);
            Variables vars = null;

            // Create a new proxy to the web service
            ReportService2005.ReportingService2005 rs = new ReportService2005.ReportingService2005();
            ReportExecution2005.ReportExecutionService rsExec = new ReportExecution2005.ReportExecutionService();

            // Authenticate to the Web service using Windows credentials
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
            rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;

            // Assign the URL of the Web service
            rs.Url = _ReportServer + "/ReportService2005.asmx";
            rsExec.Url = _ReportServer + "/ReportExecution2005.asmx";
            rs.Timeout = _TimeOut;
            rsExec.Timeout = _TimeOut;
            
            componentEvents.FireInformation(0, "ReportGenerator", "Report Server:" + _ReportServer, string.Empty, 0, ref _refire);

            if (Snapshot == true)
            {
                try
                {
                    SSISComponents.ReportGenerator.ReportService2005.Warning[] warn;
                    rs.CreateReportHistorySnapshot(Reportname, out warn);
                }
                catch (Exception ex)
                {
                    componentEvents.FireWarning(0, "ReportGeneratorTask", "A snapshot could not be created. Exception: " + ex.Message, "", 0);
                }
            }

            // Prepare Render arguments
            string encoding = String.Empty;
            string mimeType = String.Empty;
            string extension = String.Empty;
            string historyID = null;
            string deviceInfo = null;
            string format = null;

            RenderExtensions renderExtensions = new RenderExtensions();
            RenderExtension renderExtension = renderExtensions.Get(fi.Extension);
            if (renderExtension == null) throw new Exception("Not supported render type (GenerateServerReport): " + fi.Extension);
            format = renderExtension.Name;

            Byte[] results;
            ReportExecution2005.Warning[] warnings = null;
            string[] streamIDs = null;

            // fire render arguments
            componentEvents.FireInformation(0, "ReportGenerator", "Reportname: " + Reportname, string.Empty, 0, ref _refire);
            componentEvents.FireInformation(0, "ReportGenerator", "Filename: " + _localname, string.Empty, 0, ref _refire);
            componentEvents.FireInformation(0, "ReportGenerator", "Format: " + format, string.Empty, 0, ref _refire);

            try
            {
                // Prepare report parameter.
                DataTable dt = (DataTable)ReportGenerator.DeserializeObject(_Reportparameter.ToString());
                ReportExecution2005.ParameterValue[] param = new ReportExecution2005.ParameterValue[dt.Rows.Count];

                // set paramter for server report
                int iParams = 0;
                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    if (!String.IsNullOrEmpty(dt.Rows[i][0].ToString()))
                    {
                        if (dt.Rows[i][0].ToString() != "Choose a Variable")
                        {
                            variableDispenser.LockForRead(dt.Rows[i][0].ToString());
                            variableDispenser.GetVariables(ref vars);
                            string var = vars[dt.Rows[i][0].ToString()].Value.ToString();
                            param[i] = new ReportExecution2005.ParameterValue();
                            param[i].Name = dt.Rows[i][1].ToString();
                            param[i].Value = var;
                            componentEvents.FireInformation(0, "ReportGenerator", dt.Rows[i][1].ToString() + " --> " + dt.Rows[i][0].ToString() + " --> " + var, string.Empty, 0, ref _refire);
                        }
                        iParams += 1;
                    }
                }

                //componentEvents.FireInformation(0, "ReportGenerator", iParams.ToString(), string.Empty, 0, ref _refire);
                // Load the selected report
                ReportExecution2005.ExecutionInfo ei = rsExec.LoadReport(Reportname, historyID);

                componentEvents.FireInformation(0, "ReportGenerator", "ExecutionDateTime: " + ei.ExecutionDateTime.ToLongDateString(), string.Empty, 0, ref _refire);
                componentEvents.FireInformation(0, "ReportGenerator", "NumPages: " + ei.NumPages.ToString(), string.Empty, 0, ref _refire);

                // Set the parameters
                if (iParams > 0)
                {
                    rsExec.SetExecutionParameters(param, "en-EN");
                }

                // Render the report
                results = rsExec.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
                
                //// Create a file stream and write the report to it
                using (FileStream stream = File.OpenWrite(_localname))
                {
                    stream.Write(results, 0, results.Length);
                }

                componentEvents.FireInformation(0, "ReportGenerator", "Report was generated.", string.Empty, 0, ref _refire);

                // open ReportViewerUI in debugmode
                if (_DebugMode)
                {
                    componentEvents.FireInformation(0, "ReportGenerator", "Show Report in Debugmode.", string.Empty, 0, ref _refire);
                    SSISComponents.ReportGenerator.ReportViewerUI localReport = new SSISComponents.ReportGenerator.ReportViewerUI();
                    localReport.Reportserver = ReportServer;
                    localReport.Reportname = Reportname;
                    localReport.WindowsAuthorization = WindowsAuthorization;
                    localReport.Username = Username;
                    localReport.Password = Password;

                    //set parameter for localreport
                    if (iParams > 0)
                    {
                        ReportParameter[] parameter = new ReportParameter[iParams];

                        for (int i = 0; i <= dt.Rows.Count - 1; i++)
                        {
                            if (!String.IsNullOrEmpty(dt.Rows[i][0].ToString()))
                            {
                                variableDispenser.LockForRead(dt.Rows[i][0].ToString());
                                variableDispenser.GetVariables(ref vars);
                                string var = vars[dt.Rows[i][0].ToString()].Value.ToString();
                                ReportParameter localparam = new ReportParameter();
                                localparam.Name = dt.Rows[i][1].ToString();
                                localparam.Values.Add(var);
                                parameter[i] = localparam;
                            }
                        }

                        localReport.Reportparameter = parameter;
                    }

                    localReport.ShowDialog();
                }
            }
            catch (Exception ex)
            {
                componentEvents.FireError(0, "ReportGenerator", ex.ToString(), "", 0);
            }
        }

        /// <summary>
        /// Set filename with prefix to local variable
        /// </summary>
        /// <param name="variableDispenser"></param>
        /// <param name="componentEvents"></param>
        private void SetPrefixFilename(VariableDispenser variableDispenser, IDTSComponentEvents componentEvents)
        {
            try
            {
                if (PrefixFilename != null && PrefixFilename != "Choose a Variable")
                {
                    Variables vars = null;
                    variableDispenser.LockOneForWrite(PrefixFilename, ref vars);
                    vars[PrefixFilename].Value = TypeConverter(_localname, vars[0].DataType);
                    vars.Unlock();
                    variableDispenser.Reset();
                    componentEvents.FireInformation(0, "ReportGenerator", "Variable " + PrefixFilename + " set to " + _localname, string.Empty, 0, ref _refire);
                }
            }
            catch (Exception exc)
            {
                componentEvents.FireError(0, "ReportGenerator", "Task Errored: " + exc.ToString(), "", -1);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="value"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private object TypeConverter(string value, TypeCode type)
        {
            switch (type)
            {
                case TypeCode.Boolean:
                    return bool.Parse(value);
                case TypeCode.Byte:
                    return byte.Parse(value);
                case TypeCode.Char:
                    return char.Parse(value);
                case TypeCode.DBNull:
                    return null;
                case TypeCode.DateTime:
                    return DateTime.Parse(value);
                case TypeCode.Decimal:
                    return decimal.Parse(value);
                case TypeCode.Double:
                    return double.Parse(value);
                case TypeCode.Empty:
                    return null;
                case TypeCode.Int16:
                    return Int16.Parse(value);
                case TypeCode.Int32:
                    return Int32.Parse(value);
                case TypeCode.Int64:
                    return Int64.Parse(value);
                case TypeCode.Object:
                    return value;
                case TypeCode.SByte:
                    return SByte.Parse(value);
                case TypeCode.Single:
                    return Single.Parse(value);
                case TypeCode.String:
                    return value;
                case TypeCode.UInt16:
                    return UInt16.Parse(value);
                case TypeCode.UInt32:
                    return UInt32.Parse(value);
                case TypeCode.UInt64:
                    return UInt64.Parse(value);
                default:
                    return value;
            }
        }

        /// <summary>
        /// Generates the Filename with prefix
        /// </summary>
        /// <returns></returns>
        public string Localname()
        {
            string file = null;
            if (_Prefix)
            {
                FileInfo fi = new FileInfo(_Filename);
                string localPrefix = DateTime.Now.Year.ToString() +
                                    TwoChar(DateTime.Now.Month.ToString()) +
                                    TwoChar(DateTime.Now.Day.ToString()) +
                                    TwoChar(DateTime.Now.Hour.ToString()) +
                                    TwoChar(DateTime.Now.Minute.ToString()) +
                                    TwoChar(DateTime.Now.Second.ToString()) + "_";
                file = fi.DirectoryName + "\\" + localPrefix + fi.Name;
            }
            else
            {
                file = _Filename;
            }
            return file;
        }

        /// <summary>
        /// Serialize parameter settings
        /// </summary>
        /// <param name="Parameter"></param>
        /// <returns></returns>
        public static string SerializeObject(object Parameter)
        {
            BinaryFormatter formatter = new BinaryFormatter();
            MemoryStream stream = new MemoryStream();
            formatter.Serialize(stream, Parameter);
            string text = Convert.ToBase64String(stream.GetBuffer());
            stream.Close();
            return text;
        }

        /// <summary>
        /// Deserialize parameter settings
        /// </summary>
        /// <param name="Parameter"></param>
        /// <returns></returns>
        public static object DeserializeObject(string Parameter)
        {
            BinaryFormatter formatter = new BinaryFormatter();
            Byte[] str = Convert.FromBase64String(Parameter);
            MemoryStream stream = new MemoryStream(str);
            object objParam = formatter.Deserialize(stream);
            return objParam;
        }

        #endregion
    }
}

